package org.github.xhjcehust.dubbo.proxy.util;

import cn.htd.center.central.member.dto.CusMemberBaseInfoDTO;
import cn.htd.center.central.member.dto.CusMemberOutsideSupplierCompanyDTO;
import cn.htd.membercenter.domain.MemberBaseInfo;
import cn.htd.membercenter.dto.MemberBaseInfoDTO;
import cn.htd.membercenter.dto.MemberBaseInfoRegisterDTO;
import cn.htd.usercenter.dto.UserDTO;
import cn.htd.zeus.tc.dto.resquest.OuterOrderImportRequestDTO;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

public class ExcelData {
    private XSSFSheet sheet;

    /**
     * 构造函数，初始化excel数据
     * @param filePath  excel路径
     * @param sheetName sheet表名
     */
    public ExcelData(String filePath,String sheetName){
        FileInputStream fileInputStream = null;
        try {
            fileInputStream = new FileInputStream(filePath);
            XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream);
            //获取sheet
            sheet = sheets.getSheet(sheetName);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据行和列的索引获取单元格的数据
     * @param row
     * @param column
     * @return
     */
    public String getExcelDateByIndex(int row,int column){
        XSSFRow row1 = sheet.getRow(row);
        String cell = row1.getCell(column).toString();
        return cell;
    }

    /**
     * 根据某一列值为“******”的这一行，来获取该行第x列的值
     * @param caseName
     * @param currentColumn 当前单元格列的索引
     * @param targetColumn 目标单元格列的索引
     * @return
     */
    public String getCellByCaseName(String caseName,int currentColumn,int targetColumn){
        String operateSteps="";
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=0;i<rows;i++){
            XSSFRow row = sheet.getRow(i);
            String cell = row.getCell(currentColumn).toString();
            if(cell.equals(caseName)){
                operateSteps = row.getCell(targetColumn).toString();
                break;
            }
        }
        return operateSteps;
    }

    public List<MemberBaseInfoRegisterDTO> readSMExcelData(){
        List<MemberBaseInfoRegisterDTO> list = new ArrayList<>();
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=1;i<rows;i++){//从第二行开始
            //获取列数
            XSSFRow row = sheet.getRow(i);

            MemberBaseInfoRegisterDTO dto = new MemberBaseInfoRegisterDTO();
            dto.setCompanyName(row.getCell(1).toString().trim()+"的小店");
            dto.setInvoiceNotify("南京市数美网络科技有限公司");
            dto.setArtificialPersonName(row.getCell(1).toString().trim());
            dto.setBusinessPersonName(row.getCell(1).toString().trim());

            DataFormatter dataFormatter = new DataFormatter();
            dataFormatter.addFormat("###########", null);
            String cell = dataFormatter.formatCellValue(row.getCell(2));
            dto.setArtificialPersonMobile(cell);
            dto.setBusinessPersonMobile(cell);
            dto.setMemberCode(row.getCell(3).toString().trim());
            dto.setBusinessScope("1");
            dto.setLocationProvince("32");
            dto.setLocationCity("3201");
            dto.setLocationCounty("320102");
            dto.setLocationTown("320102009");
            dto.setLocationDetail("南京市玄武区钟灵街50号");
            dto.setLocationAddr("江苏南京市玄武区南京市玄武区钟灵街50号");
            dto.setActualBusinessProvince("32");
            dto.setActualBusinessCity("3201");
            dto.setActualBusinessCounty("320102");
            dto.setActualBusinessTown("320102009");
            dto.setActualBusinessDetail("南京市玄武区钟灵街50号");
            dto.setActualBusinessAddress("江苏南京市玄武区南京市玄武区钟灵街50号");
//            dto.setCustomerNature("个体工商户");
            dto.setHasBusinessLicense(1);
            dto.setBuyerBusinessLicenseId("91320102MA20TLPU0R");
            dto.setRegistFrom("9");
            dto.setCreateName("人人开店");
            dto.setSellerLabel("2");
            list.add(dto);
        }

        return list;
    }

    public List<MemberBaseInfoRegisterDTO> decrptExcel(){
        List<MemberBaseInfoRegisterDTO> list = new ArrayList<>();
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=1;i<rows;i++){//从第二行开始
            //获取列数
            XSSFRow row = sheet.getRow(i);

            MemberBaseInfoRegisterDTO dto = new MemberBaseInfoRegisterDTO();
            if (row.getCell(0)!=null){
                dto.setMemberCode(row.getCell(0).toString().trim());
            }
            if (row.getCell(1)!=null){
                dto.setCompanyName(row.getCell(1).toString().trim());
            }
            if (row.getCell(2)!=null){
                dto.setTaxManId(row.getCell(2).toString().trim());
            }
            if (row.getCell(3)!=null){
                dto.setInvoiceAddress(row.getCell(3).toString().trim());
            }
            if (row.getCell(4)!=null){
                dto.setContactPhone(row.getCell(4).toString().trim());
            }
            if (row.getCell(5)!=null){
                dto.setBankName(row.getCell(5).toString().trim());
            }
            if (row.getCell(6)!=null){
                dto.setBankAccount(row.getCell(6).toString().trim());
            }
            list.add(dto);
        }

        return list;
    }

    public List<OuterOrderImportRequestDTO> readOutOrder(){
        List<OuterOrderImportRequestDTO> list = new ArrayList<>();
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=1;i<rows;i++){//从第二行开始
            //获取列数
            XSSFRow row = sheet.getRow(i);

            OuterOrderImportRequestDTO dto = new OuterOrderImportRequestDTO();
            if (row.getCell(0)!=null){
                dto.setOriginalOrderNo(row.getCell(0).toString().trim());
            }
            if (row.getCell(1)!=null){
                dto.setReceiverName(row.getCell(1).toString().trim());
            }
            if (row.getCell(2)!=null){
                DataFormatter dataFormatter = new DataFormatter();
                dataFormatter.addFormat("###########", null);
                String cell = dataFormatter.formatCellValue(row.getCell(2));
                dto.setContactPhone(cell);
            }
            if (row.getCell(3)!=null){
                dto.setConsigneeAddress(row.getCell(3).toString().trim());
            }
            if (row.getCell(4)!=null){
            }
            if (row.getCell(5)!=null){
                dto.setWarehouseType(Float.valueOf(row.getCell(5).toString().trim()).intValue());
            }
            dto.setRegularOrder(true);
            list.add(dto);
        }

        return list;
    }

    public List<String> readWeakPwd(){
        List<String> list = new ArrayList<>();
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=1;i<rows;i++){//从第二行开始
            //获取列数
            XSSFRow row = sheet.getRow(i);

            if (row.getCell(0)!=null){
                list.add(row.getCell(0).toString().trim());
            }
        }

        return list;
    }

    public List<CusMemberBaseInfoDTO> readNoMember(){
        List<CusMemberBaseInfoDTO> list = new ArrayList<>();
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=1;i<rows;i++){//从第二行开始
            //获取列数
            XSSFRow row = sheet.getRow(i);

            CusMemberBaseInfoDTO dto = new CusMemberBaseInfoDTO();
            dto.setId(Long.valueOf(row.getCell(0).toString()));
            dto.setMemberCode(row.getCell(1).toString().trim());
            if(row.getCell(2)==null){//公司名称
                continue;
            }
            dto.setCompanyName(row.getCell(2).toString().trim());
            if(row.getCell(3)!=null){//手机号
                dto.setBusinessPersonMobile(row.getCell(3).toString().trim());
            }
            dto.setDataTag(Float.valueOf(row.getCell(4).toString()).intValue());
            list.add(dto);
        }

        return list;
    }

    public List<MemberBaseInfoRegisterDTO> readJMExcelData(){
        List<MemberBaseInfoRegisterDTO> list = new ArrayList<>();
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=1;i<rows;i++){//从第二行开始
            //获取列数
            XSSFRow row = sheet.getRow(i);

            DataFormatter dataFormatter = new DataFormatter();
            dataFormatter.addFormat("###########", null);
            String cell = dataFormatter.formatCellValue(row.getCell(1));
            MemberBaseInfoRegisterDTO dto = new MemberBaseInfoRegisterDTO();
            double memberId = row.getCell(0).getNumericCellValue();
            dto.setMemberId(Double.valueOf(memberId).longValue());
            dto.setBusinessPersonMobile(cell);
            list.add(dto);
        }

        return list;
    }

    public List<MemberBaseInfoDTO> readLicence(){
        List<MemberBaseInfoDTO> list = new ArrayList<>();
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=0;i<rows;i++){
            //获取列数
            XSSFRow row = sheet.getRow(i);

            MemberBaseInfoDTO dto = new MemberBaseInfoDTO();
            row.getCell(3).setCellType(CellType.STRING);
            String licenceId = row.getCell(3).getStringCellValue();
            dto.setBuyerBusinessLicenseId(licenceId);
            row.getCell(4).setCellType(CellType.STRING);
            String companyCode = row.getCell(4).getStringCellValue();
            dto.setCompanyCode(companyCode);
            list.add(dto);
        }

        return list;
    }

    public List<String> readMemberCodes(){
        List<String> list = new ArrayList<>();
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=1;i<rows;i++){//从第二行开始
            //获取列数
            XSSFRow row = sheet.getRow(i);
            String cell = row.getCell(0).toString();
            list.add(cell);
        }

        return list;
    }

    public List<MemberBaseInfoRegisterDTO> readNoStorePositionExcelData(){
        List<MemberBaseInfoRegisterDTO> list = new ArrayList<>();
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=1;i<rows;i++){//从第二行开始
            //获取列数
            XSSFRow row = sheet.getRow(i);

            DataFormatter dataFormatter = new DataFormatter();
            dataFormatter.addFormat("###########", null);
            String cell = dataFormatter.formatCellValue(row.getCell(1));

            double memberId = Long.valueOf(row.getCell(0).getStringCellValue());
            String companyName = row.getCell(1).getStringCellValue();
            String locationCounty = row.getCell(2).getStringCellValue();
            String locationTown = row.getCell(3).getStringCellValue();
            String locationDetail = row.getCell(4).getStringCellValue();

            MemberBaseInfoRegisterDTO dto = new MemberBaseInfoRegisterDTO();
            dto.setMemberId(Double.valueOf(memberId).longValue());
            dto.setCompanyName(companyName);
            dto.setLocationCounty(locationCounty);
            dto.setLocationTown(locationTown);
            dto.setLocationDetail(locationDetail);
            list.add(dto);
        }

        return list;
    }

    public List<CusMemberOutsideSupplierCompanyDTO> readCompanyInfo(){
        List<CusMemberOutsideSupplierCompanyDTO> list = new ArrayList<>();
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=1;i<rows;i++){//从第二行开始
            //获取列数
            XSSFRow row = sheet.getRow(i);

            CusMemberOutsideSupplierCompanyDTO dto = new CusMemberOutsideSupplierCompanyDTO();
            if(row.getCell(0)==null){//memberCode为空跳过
                continue;
            }
            dto.setMemberCode(row.getCell(0).toString().trim());
            if(row.getCell(1)!=null){//公司名称
                dto.setCompanyName(row.getCell(1).toString().trim());
            }
            if(row.getCell(2)!=null){//经营人手机号
                dto.setBusinessPersonMobile(row.getCell(2).toString().trim());
            }
            if(row.getCell(3)!=null){//法人手机号
                dto.setArtificialPersonMobile(row.getCell(3).toString().trim());
            }
            if(row.getCell(4)!=null){//注册地址
                dto.setLocationAddr(row.getCell(4).toString().trim());
            }
            if(row.getCell(5)!=null){//经营地址
                dto.setActualBusinessAddress(row.getCell(5).toString().trim());
            }
            if(row.getCell(6)!=null){//经营地址详情
                dto.setLocationDetail(row.getCell(6).toString().trim());
            }
            if(row.getCell(7)!=null){//经营地址详情
                dto.setActualBusinessDetail(row.getCell(7).toString().trim());
            }
            list.add(dto);
        }

        return list;
    }

    public List<CusMemberBaseInfoDTO> readAccountNo(){
        List<CusMemberBaseInfoDTO> list = new ArrayList<>();
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=1;i<rows;i++){//从第二行开始
            //获取列数
            XSSFRow row = sheet.getRow(i);

            CusMemberBaseInfoDTO dto = new CusMemberBaseInfoDTO();
            dto.setMemberCode(row.getCell(0).toString().trim());
            dto.setArtificialPersonName(row.getCell(1).toString().trim());
            dto.setLocationProvince(row.getCell(2).toString().trim());
            dto.setLocationCity(row.getCell(3).toString().trim());
            dto.setLocationCounty(row.getCell(4).toString().trim());
            dto.setLocationTown(row.getCell(5).toString().trim());
            dto.setLocationDetail(row.getCell(6).toString().trim());
            dto.setLocationAddr(dto.getLocationProvince()+dto.getLocationCity()+dto.getLocationCounty()+dto.getLocationTown()+dto.getLocationDetail());
            list.add(dto);
        }

        return list;
    }

    //测试方法
//    public static void main(String[] args){
//        ExcelData sheet1 = new ExcelData("C:\\Users\\08011707\\Desktop\\数美会员数据导入.xlsx", "Sheet1");
//        List<MemberBaseInfoRegisterDTO> list = sheet1.readExcelData();
//        System.out.println(JSONObject.toJSONString(list));
//    }
}
